package dao;

import model.CreditLog;
import model.User;
import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 信誉分数据访问对象
 */
public class CreditDAO {

    /**
     * 添加信誉分记录
     */
    public boolean insertCreditLog(CreditLog log) throws SQLException {
        String sql = "INSERT INTO credit_logs (user_id, attendance_id, score_change, score_after, reason, log_type) " +
                "VALUES (?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setInt(1, log.getUserId());
            if (log.getAttendanceId() > 0) {
                stmt.setInt(2, log.getAttendanceId());
            } else {
                stmt.setNull(2, Types.INTEGER);
            }
            stmt.setInt(3, log.getScoreChange());
            stmt.setInt(4, log.getScoreAfter());
            stmt.setString(5, log.getReason());
            stmt.setInt(6, log.getLogType());

            int result = stmt.executeUpdate();
            if (result > 0) {
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        log.setLogId(rs.getInt(1));
                    }
                }
                return true;
            }
            return false;
        }
    }

    /**
     * 获取用户的信誉分记录
     */
    public List<CreditLog> getUserCreditLogs(int userId, LocalDate startDate, LocalDate endDate) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT cl.*, u.student_id, u.name as user_name ");
        sql.append("FROM credit_logs cl ");
        sql.append("LEFT JOIN users u ON cl.user_id = u.user_id ");
        sql.append("WHERE cl.user_id = ? ");

        List<Object> params = new ArrayList<>();
        params.add(userId);

        if (startDate != null && endDate != null) {
            sql.append("AND DATE(cl.create_time) BETWEEN ? AND ? ");
            params.add(Date.valueOf(startDate));
            params.add(Date.valueOf(endDate));
        }

        sql.append("ORDER BY cl.create_time DESC");

        List<CreditLog> logs = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    CreditLog log = mapResultSetToCreditLog(rs);

                    // 设置用户信息
                    User user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setStudentId(rs.getString("student_id"));
                    user.setName(rs.getString("user_name"));
                    log.setUser(user);

                    logs.add(log);
                }
            }
        }
        return logs;
    }

    /**
     * 获取所有信誉分记录（管理员使用）
     */
    public List<CreditLog> getAllCreditLogs(LocalDate startDate, LocalDate endDate, Integer logType)
            throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT cl.*, u.student_id, u.name as user_name ");
        sql.append("FROM credit_logs cl ");
        sql.append("LEFT JOIN users u ON cl.user_id = u.user_id ");
        sql.append("WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (startDate != null && endDate != null) {
            sql.append("AND DATE(cl.create_time) BETWEEN ? AND ? ");
            params.add(Date.valueOf(startDate));
            params.add(Date.valueOf(endDate));
        }

        if (logType != null) {
            sql.append("AND cl.log_type = ? ");
            params.add(logType);
        }

        sql.append("ORDER BY cl.create_time DESC");

        List<CreditLog> logs = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    CreditLog log = mapResultSetToCreditLog(rs);

                    // 设置用户信息
                    User user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setStudentId(rs.getString("student_id"));
                    user.setName(rs.getString("user_name"));
                    log.setUser(user);

                    logs.add(log);
                }
            }
        }
        return logs;
    }

    /**
     * 管理员调整用户信誉分
     */
    public boolean adjustUserCreditScore(int userId, int scoreChange, String reason) throws SQLException {
        Connection conn = null;
        try {
            conn = DatabaseConnection.getConnection();
            conn.setAutoCommit(false);

            // 更新用户信誉分
            String updateUserSql = "UPDATE users SET credit_score = LEAST(150, GREATEST(0, credit_score + ?)) WHERE user_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(updateUserSql)) {
                stmt.setInt(1, scoreChange);
                stmt.setInt(2, userId);
                stmt.executeUpdate();
            }

            // 获取更新后的信誉分
            int newScore;
            String getScoreSql = "SELECT credit_score FROM users WHERE user_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(getScoreSql)) {
                stmt.setInt(1, userId);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        newScore = rs.getInt("credit_score");
                    } else {
                        throw new SQLException("用户不存在");
                    }
                }
            }

            // 插入信誉分记录 - 使用同一个连接而不是新连接
            String insertLogSql = "INSERT INTO credit_logs (user_id, attendance_id, score_change, score_after, reason, log_type) "
                    +
                    "VALUES (?, ?, ?, ?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(insertLogSql)) {
                stmt.setInt(1, userId);
                stmt.setNull(2, Types.INTEGER); // attendance_id为空
                stmt.setInt(3, scoreChange);
                stmt.setInt(4, newScore);
                stmt.setString(5, reason);
                stmt.setInt(6, 2); // 2表示管理员调整
                stmt.executeUpdate();
            }

            // 检查是否需要禁用用户
            if (newScore < 20) {
                String banUserSql = "UPDATE users SET is_banned = TRUE, ban_until_date = DATE_ADD(CURDATE(), INTERVAL 7 DAY) WHERE user_id = ?";
                try (PreparedStatement stmt = conn.prepareStatement(banUserSql)) {
                    stmt.setInt(1, userId);
                    stmt.executeUpdate();
                }
            }

            conn.commit();
            return true;

        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException rollbackEx) {
                    System.err.println("回滚失败: " + rollbackEx.getMessage());
                }
            }
            throw new SQLException("调整信誉分失败: " + e.getMessage(), e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                } catch (SQLException ex) {
                    System.err.println("恢复自动提交失败: " + ex.getMessage());
                }
                try {
                    conn.close();
                } catch (SQLException ex) {
                    System.err.println("关闭连接失败: " + ex.getMessage());
                }
            }
        }
    }

    /**
     * 获取信誉分统计信息
     */
    public CreditStatistics getCreditStatistics(int userId) throws SQLException {
        String sql = "SELECT " +
                "COUNT(*) as total_logs, " +
                "SUM(CASE WHEN score_change > 0 THEN score_change ELSE 0 END) as total_gained, " +
                "SUM(CASE WHEN score_change < 0 THEN ABS(score_change) ELSE 0 END) as total_lost, " +
                "AVG(score_after) as avg_score " +
                "FROM credit_logs WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new CreditStatistics(
                            rs.getInt("total_logs"),
                            rs.getInt("total_gained"),
                            rs.getInt("total_lost"),
                            rs.getDouble("avg_score"));
                }
            }
        }
        return new CreditStatistics(0, 0, 0, 100.0);
    }

    /**
     * 获取系统信誉分概览
     */
    public SystemCreditOverview getSystemCreditOverview() throws SQLException {
        String sql = "SELECT " +
                "COUNT(*) as total_users, " +
                "AVG(credit_score) as avg_score, " +
                "SUM(CASE WHEN credit_score >= 120 THEN 1 ELSE 0 END) as excellent_users, " +
                "SUM(CASE WHEN credit_score >= 80 THEN 1 ELSE 0 END) as good_users, " +
                "SUM(CASE WHEN credit_score < 50 THEN 1 ELSE 0 END) as poor_users, " +
                "SUM(CASE WHEN is_banned THEN 1 ELSE 0 END) as banned_users " +
                "FROM users WHERE user_type = 0";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new SystemCreditOverview(
                            rs.getInt("total_users"),
                            rs.getDouble("avg_score"),
                            rs.getInt("excellent_users"),
                            rs.getInt("good_users"),
                            rs.getInt("poor_users"),
                            rs.getInt("banned_users"));
                }
            }
        }
        return new SystemCreditOverview(0, 0, 0, 0, 0, 0);
    }

    /**
     * 将ResultSet映射到CreditLog对象
     */
    private CreditLog mapResultSetToCreditLog(ResultSet rs) throws SQLException {
        CreditLog log = new CreditLog();

        log.setLogId(rs.getInt("log_id"));
        log.setUserId(rs.getInt("user_id"));

        int attendanceId = rs.getInt("attendance_id");
        if (!rs.wasNull()) {
            log.setAttendanceId(attendanceId);
        }

        log.setScoreChange(rs.getInt("score_change"));
        log.setScoreAfter(rs.getInt("score_after"));
        log.setReason(rs.getString("reason"));
        log.setLogType(rs.getInt("log_type"));
        log.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());

        return log;
    }

    /**
     * 信誉分统计信息类
     */
    public static class CreditStatistics {
        private int totalLogs;
        private int totalGained;
        private int totalLost;
        private double avgScore;

        public CreditStatistics(int totalLogs, int totalGained, int totalLost, double avgScore) {
            this.totalLogs = totalLogs;
            this.totalGained = totalGained;
            this.totalLost = totalLost;
            this.avgScore = avgScore;
        }

        public int getTotalLogs() {
            return totalLogs;
        }

        public int getTotalGained() {
            return totalGained;
        }

        public int getTotalLost() {
            return totalLost;
        }

        public double getAvgScore() {
            return avgScore;
        }
    }

    /**
     * 系统信誉分概览类
     */
    public static class SystemCreditOverview {
        private int totalUsers;
        private double avgScore;
        private int excellentUsers;
        private int goodUsers;
        private int poorUsers;
        private int bannedUsers;

        public SystemCreditOverview(int totalUsers, double avgScore, int excellentUsers,
                int goodUsers, int poorUsers, int bannedUsers) {
            this.totalUsers = totalUsers;
            this.avgScore = avgScore;
            this.excellentUsers = excellentUsers;
            this.goodUsers = goodUsers;
            this.poorUsers = poorUsers;
            this.bannedUsers = bannedUsers;
        }

        public int getTotalUsers() {
            return totalUsers;
        }

        public double getAvgScore() {
            return avgScore;
        }

        public int getExcellentUsers() {
            return excellentUsers;
        }

        public int getGoodUsers() {
            return goodUsers;
        }

        public int getPoorUsers() {
            return poorUsers;
        }

        public int getBannedUsers() {
            return bannedUsers;
        }
    }
}
